package com.huanying.goodjob.callcenter;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.huanying.framework.BaseDao;
import com.huanying.framework.PageBean;
import com.huanying.goodjob.job.Jobitem_User;

@Service
public class CallcenterServiceImpl implements CallcenterService{
	
	@Autowired
	private BaseDao dao;
	@Override
	public void add_jobitem_call(Jobitem_call jobitem_call) throws Exception {
		// TODO Auto-generated method stub
		dao.save(jobitem_call);
	}

	@Override
	public List search_add_jobitem_calls() throws Exception {
		// TODO Auto-generated method stub
	//	String hql ="from Jobitem_User as j where ((j.jobitem.jobitem_status=2) or (j.rework_status=1 and j.status=1 and j.jobitem.id in (select jch1.jobitem.id from Jobitemstatus_Change as jch1 where jch1.id in (select max(jch.id) from Jobitemstatus_Change as jch where jch.jobitem_status=5 and jch.status=1 and TIMESTAMPDIFF(HOUR,DATE_FORMAT(jch.time,'%Y-%m-%d %H:%i'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'))>=2 group by jch.jobitem.id)) and j.jobitem.jobitem_status=5) or (j.jobitem.jobitem_status=5 and j.jobitem.id in (select jc1.jobitem.id from Jobitemstatus_Change as jc1 where jc1.id in (select max(jc.id) from Jobitemstatus_Change as jc where jc.jobitem_status=5 and jc.status=1 and TIMESTAMPDIFF(HOUR,DATE_FORMAT(jc.time,'%Y-%m-%d %H:%i'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'))>=2 group by jc.jobitem.id)) and j.rework_status=0 and j.jobitem_status=1 and j.jobitem.jobitem_status=5)) and j.status=1 and (j.jobitem.id not in (select jcall.jobitem.id from Jobitem_call as jcall where jcall.status=1 and jcall.call_status=0)) order by j.id desc";
		String hql1="select j.* from jobitem_user j,jobitem jitem where j.jobitem_id=jitem.id and jitem.jobitem_status=2 and j.status=1 and (j.jobitem_id not in (select jcall.jobitem_id from jobitem_call as jcall where jcall.status=1 and jcall.call_status=0))";
		String hql2="select j.* from jobitem_user j,jobitem jitem where j.jobitem_id=jitem.id and (j.rework_status=1 and j.status=1 and jitem.id in (select jch1.jobitem_id from jobitemstatus_change as jch1 where jch1.id in (select max(jch.id) from jobitemstatus_change jch where jch.jobitem_status=5 and jch.status=1 and TIMESTAMPDIFF(HOUR,DATE_FORMAT(jch.time,'%Y-%m-%d %H:%i'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'))>=2 group by jch.jobitem_id)) and jitem.jobitem_status=5) and j.status=1 and (j.jobitem_id not in (select jcall.jobitem_id from jobitem_call as jcall where jcall.status=1 and jcall.call_status=0))";
		String hql3="select j.* from jobitem_user j,jobitem jitem where j.jobitem_id=jitem.id and (jitem.jobitem_status=5 and j.jobitem_id in (select jc1.jobitem_id from jobitemstatus_change jc1 where jc1.id in (select max(jc.id) from jobitemstatus_change jc where jc.jobitem_status=5 and jc.status=1 and TIMESTAMPDIFF(HOUR,DATE_FORMAT(jc.time,'%Y-%m-%d %H:%i'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i'))>=2 group by jc.jobitem_id)) and j.rework_status=0 and j.jobitem_status=1 and jitem.jobitem_status=5) and j.status=1 and (j.jobitem_id not in (select jcall.jobitem_id from jobitem_call as jcall where jcall.status=1 and jcall.call_status=0))";
	//	return dao.exculeHqlByParamslines(hql, null);
		return (List<Jobitem_User>)dao.exculesqlByParams(new String(hql1+" union "+hql2+" union "+hql3),null);
	}

	@Override
	public void update_jobitem_callstatus(int id) throws Exception {
		// TODO Auto-generated method stub
		String hql="update Jobitem_call set call_status=1 where id=?";
		List<Object> param=new ArrayList<Object>();
		param.add(id);
		dao.exculeByParams(hql, param);
	}

	@Override
	public Jobitem_call get_jobitem_calls(int id) throws Exception {
		// TODO Auto-generated method stub
		return (Jobitem_call)dao.load(Jobitem_call.class,id);
	}

	@Override
	public PageBean search_jobitem_call(Map<String,Object> map,int pageSize, int page) throws Exception {
		// TODO Auto-generated method stub
		StringBuffer hql=new StringBuffer("from Jobitem_call as j where j.call_status=0 and j.status=1");
		List<Object> param=new ArrayList<Object>();
		if(map.get("user_id")!=null && map.get("user_id")!="")
		{
			hql.append(" and j.adminuser.id=?");
			param.add(Integer.parseInt((String)map.get("user_id")));
			
		}
		int allRow = dao.queryAllRowCount(hql.toString(), param);
		int totalPage = PageBean.countTotalPage(pageSize, allRow);
		final int offset = PageBean.countOffset(pageSize, page);
		final int currentPage = PageBean.countCurrentPage(page);
		List<Object> list = dao.queryForPageAndParams(hql.toString(), param, offset, pageSize);

		PageBean pageBean = new PageBean();
		pageBean.setPageSize(pageSize);
		pageBean.setCurrentPage(currentPage);
		pageBean.setAllRow(allRow);
		pageBean.setTotalPage(totalPage);
		pageBean.setList(list);
		pageBean.init();

		return pageBean;
	}

	@Override
	public void delete_jobitem_call(int jobitem_id) throws Exception {
		// TODO Auto-generated method stub
		List<Jobitem_User>  jslist= new ArrayList<Jobitem_User>();
		String jshql = "from Jobitem_User as j where j.jobitem.id =? and j.status=1 and j.jobitem_status=0 and j.rework_status=0";
		List<Object> params = new ArrayList<Object>();
		params.add(jobitem_id);
		jslist=dao.exculeHqlByParams(jshql, params);
		if(jslist.size()==0)
		{
		String hql="update Jobitem_call as jc set jc.status=0 where jc.jobitem.id=? and jc.call_status=0 and jc.status=1 and jc.jobitem_type=1";
		List<Object> param=new ArrayList<Object>();
		param.add(jobitem_id);
		dao.exculeByParams(hql, param);
		}
		
	}

	@Override
	public List search_alljobitem_call() throws Exception {
		// TODO Auto-generated method stub
		String hql="from Jobitem_call as j where j.call_status=0 and j.status=1 and j.jobitem_type=1";
		
		return dao.exculeHqlByParams(hql,null);
	}
}
